package com.cardmanager.pro.repository;

import java.util.List;

import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import com.cardmanager.pro.pojo.RepaymentTaskPOJO;
@Repository
public interface RepaymentTaskPOJORepository extends JpaRepository<RepaymentTaskPOJO, Long>,JpaSpecificationExecutor<RepaymentTaskPOJO> {



	@Query("select sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber group by repaymentTaskPOJO.userId, repaymentTaskPOJO.creditCardNumber")
	List<Object[]> queryRepaymentTaskAllamountByUserIdAndCardno(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus")
	int queryTaskStatusCount(@Param("userId") String userIdStr, @Param("creditCardNumber") String creditCardNumber, @Param("taskStatus") int taskStatus);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.taskType = 1 and repaymentTaskPOJO.planType = 1 and repaymentTaskPOJO.taskStatus = 0 and repaymentTaskPOJO.executeDateTime <=:nowTime and repaymentTaskPOJO.executeDate =:executeDate")
	List<RepaymentTaskPOJO> findTaskTypeAndTaskStatusRepaymentTaskPay( @Param("nowTime") String nowTime, @Param("executeDate") String executeDate);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.taskType = 2 and repaymentTaskPOJO.planType = 1 and repaymentTaskPOJO.taskStatus = 0 and repaymentTaskPOJO.executeDateTime <=:nowTime and repaymentTaskPOJO.executeDate =:executeDate")
	List<RepaymentTaskPOJO> findTaskTypeAndTaskStatusRepaymentTask( @Param("nowTime") String nowTime, @Param("executeDate") String executeDate);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.taskType = 1 and repaymentTaskPOJO.taskStatus <> 1  and repaymentTaskPOJO.planType = 1 and repaymentTaskPOJO.creditCardNumber =:creditCardNumber and repaymentTaskPOJO.executeDateTime <=:nowTime and repaymentTaskPOJO.executeDate =:executeDate")
	List<RepaymentTaskPOJO> findPayStatus( @Param("nowTime") String nowTime, @Param("executeDate") String executeDate,@Param("creditCardNumber")String creditCardNumber);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.taskType = 1 and repaymentTaskPOJO.taskStatus = 1  and repaymentTaskPOJO.planType = 1 and repaymentTaskPOJO.creditCardNumber =:creditCardNumber and repaymentTaskPOJO.executeDateTime <=:nowTime and repaymentTaskPOJO.executeDate =:executeDate")
	RepaymentTaskPOJO findPayStatusOn( @Param("nowTime") String nowTime, @Param("executeDate") String executeDate,@Param("creditCardNumber")String creditCardNumber);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.taskType <> 3 and repaymentTaskPOJO.planType = 1 and repaymentTaskPOJO.taskStatus = 0 and repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.executeDateTime <=:nowTime and repaymentTaskPOJO.executeDate=:executeDate")
	List<RepaymentTaskPOJO> findTaskStatus( @Param("nowTime") String nowTime, @Param("executeDate") String executeDate,@Param("userId") String userId,@Param("creditCardNumber") String creditCardNumber);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.taskType <> 3 and repaymentTaskPOJO.planType = 1 and repaymentTaskPOJO.taskStatus = 1 and repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.executeDateTime <=:nowTime and repaymentTaskPOJO.executeDate=:executeDate")
	List<RepaymentTaskPOJO> findTaskTypeAndTaskStatus( @Param("nowTime") String nowTime, @Param("executeDate") String executeDate,@Param("userId") String userId,@Param("creditCardNumber") String creditCardNumber);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.taskType=:taskType")
	int queryTaskStatusAndTaskTypeCount(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("taskStatus") int taskStatus, @Param("taskType") int taskType);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.orderStatus=:orderStatus and repaymentTaskPOJO.taskType=:taskType")
	int queryOrderStatusAndTaskTypeCount(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("orderStatus") int orderStatus, @Param("taskType") int taskType);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus")
	List<RepaymentTaskPOJO> findByTaskStatusRepaymentTask(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("taskStatus") int taskStatus, Pageable pageable);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=:taskStatus and repaymentTaskPOJO.orderStatus=:orderStatus")
	List<RepaymentTaskPOJO> findByTaskStatusAndOrderStatusRepaymentTask(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("taskStatus") int taskStatus, @Param("orderStatus") int orderStatus, Pageable pageable);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7'")
	List<RepaymentTaskPOJO> findByUserIdAndCreditCardNumber(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, Pageable pageable);

	RepaymentTaskPOJO findByRepaymentTaskId(String repaymentTaskId);

	RepaymentTaskPOJO findByOrderCode(String orderCode);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskType=:taskType and repaymentTaskPOJO.taskStatus=:taskStatus")
	RepaymentTaskPOJO findByTaskTypeAndTaskStatusRepaymentTaskPOJO(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("taskType") int taskType, @Param("taskStatus") int taskStatus);

	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,repaymentTaskPOJO.taskStatus,repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTime(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, Pageable pageable);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.createTime=:createTime")
	List<RepaymentTaskPOJO> findByCreateTime(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("createTime") String createTime);

	@Query("select count(*) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.orderStatus=:orderStatus and repaymentTaskPOJO.taskType=:taskType")
	int findByOrderStatusAndTaskTypeCount(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("orderStatus") int orderStatus, @Param("taskType") int taskType);

	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,repaymentTaskPOJO.taskStatus,repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.taskStatus=:taskStatus group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTimeAndTaskStatus(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("taskStatus") int taskStatus, Pageable pageable);

	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,repaymentTaskPOJO.taskStatus,repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.executeDate=:executeDate group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTimeAndExecuteDate0(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("executeDate") String executeDate, Pageable pageable);

	@Query("select repaymentTaskPOJO.userId,repaymentTaskPOJO.creditCardNumber,sum(repaymentTaskPOJO.amount),sum(repaymentTaskPOJO.realAmount),sum(repaymentTaskPOJO.totalServiceCharge),repaymentTaskPOJO.createTime,repaymentTaskPOJO.rate,repaymentTaskPOJO.taskStatus,repaymentTaskPOJO.executeDate,sum(repaymentTaskPOJO.returnServiceCharge) from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.executeDate>:executeDate group by repaymentTaskPOJO.createTime")
	List<Object[]> findByCreateTimeAndExecuteDate1(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber, @Param("executeDate") String date, Pageable pageable);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus!='7' and repaymentTaskPOJO.taskType='2' order by repaymentTaskPOJO.executeDate desc")
	List<RepaymentTaskPOJO> findByExecuteDate(@Param("userId") String userId, @Param("creditCardNumber") String creditCardNumber);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.taskStatus!='7'")
	List<RepaymentTaskPOJO> findByUserId(@Param("userId") String userId, Pageable pageable);

	@Query("select repaymentTaskPOJO from RepaymentTaskPOJO repaymentTaskPOJO where repaymentTaskPOJO.userId=:userId and repaymentTaskPOJO.creditCardNumber=:creditCardNumber and repaymentTaskPOJO.taskStatus=0 and repaymentTaskPOJO.channelId=:channelId")
	List<RepaymentTaskPOJO> findByUserIdAndCreditCardNumberAnAndChannelId(@Param("userId") String userId,@Param("creditCardNumber") String creditCardNumber,@Param("channelId")String channelId);

	List<RepaymentTaskPOJO> findByOrderNo(String orderNo);

	List<RepaymentTaskPOJO> findByTaskStatus(int taskStatus);


}
